﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Globalization;

namespace SomaiNordeste.FabricaRacao.DomainModel.Repository
{
    public class BatidaRepository : IBalanca
    {
        private string _connectionString;

        public BatidaRepository()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["SQLCONN"].ConnectionString;
        }

        public void Add(Batida balanca)
        {
            string queryString =
               " INSERT INTO [FabricaRacao].[fabrica].[Batidas] " +
               " ([E3TimeStamp] ,[Descricao] " +
               " ,[Descricao_Quality] ,[Tipo] " +
               " ,[Tipo_Quality] ,[Linhagem] " +
               " ,[Linhagem_Quality] ,[Versao] " +
               " ,[Versao_Quality] ,[Batida] " +
               " ,[Batida_Quality] ,[KG_Total] " +
               " ,[KG_Total_Quality] ,[Batida_Diaria] " +
               " ,[Batida_Diaria_Quality] ,[KG_Total_Atualizacao]) " +
               " VALUES " +
               "('" + FormateDate( balanca ) + "' " +
               ",'" + balanca.Descricao + "' " +
               ",'" + balanca.Descricao_Quality + "' " +
               ",'" + balanca.Tipo + "' " +
               ",'" + balanca.Tipo_Quality + "' " +
               ",'" + balanca.Linhagem + "' " +
               ",'" + balanca.Linhagem_Quality + "' " +
               ",'" + balanca.Versao + "' " +
               ",'" + balanca.Versao_Quality + "' " +
               ",'" + balanca.BatidaN + "' " +
               ",'" + balanca.Batida_Quality + "' " +
               ",'" + balanca.KG_Total.ToString().Replace(",", ".") + "' " +
               ",'" + balanca.KG_Total_Quality + "' " +
               ",'" + balanca.Batida_Diaria + "' " +
               ",'" + balanca.Batida_Diaria_Quality + "' " +
               ",'" + balanca.KG_Total_Atualizado.ToString().Replace(",", ".") + "' )";

            using (SqlConnection connection =
                   new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void Save(Batida balanca)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Batida> FindAll()
        {
            throw new NotImplementedException();
        }

        public Insumo FindBy(Batida balanca)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Batida> FindBy()
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Batida> FindBy(DateTime date)
        {
            string queryString =
                " SELECT [Descricao] " +
                " FROM [FabricaRacao].[fabrica].[Batidas] " +
                " WHERE convert(varchar, E3TimeStamp,103) = '" + date.ToShortDateString() + "' " +
                " GROUP BY [Descricao] ";

            using (SqlConnection connection =
                   new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;

                connection.Open();

                IList<Batida> batidas = new List<Batida>();
                batidas.Add(new Batida(){Descricao = "Escolha a Ração"});
                using (SqlDataReader reader = command.ExecuteReader())
                {                
                    while (reader.Read())
                    {
                        Batida batida = new Batida();
                        batida.Descricao = reader.GetString(0);
                        batidas.Add(batida);
                    }

                    return batidas;
                }
            }
        }

        public void Update(Batida batida)
        {
            string batidaUpdate = 
                      " UPDATE [FabricaRacao].[fabrica].[Batidas] "+
                      " SET [Descricao] = '"+batida.Descricao+"' "+
                      "    ,[Descricao_Quality] = '"+batida.Descricao_Quality+"' "+
                      "    ,[Tipo] = '"+batida.Tipo+"' "+
                      "    ,[Tipo_Quality] = '"+batida.Tipo_Quality+"' "+
                      "    ,[Linhagem] = '"+batida.Linhagem+"' "+
                      "    ,[Linhagem_Quality] = '"+batida.Linhagem_Quality+"' "+
                      "    ,[Versao] = '"+batida.Versao_Quality+"' "+
                      "    ,[Versao_Quality] = '"+batida.Versao_Quality+"' "+
                      "    ,[Batida] = '"+batida.BatidaN+"' "+
                      "    ,[Batida_Quality] = '"+batida.Batida_Quality+"' "+
                      "    ,[KG_Total_Atualizacao] = '" + batida.KG_Total_Atualizado.ToString().Replace(",", ".") + "' " +
                      "    ,[KG_Total_Quality] = '"+batida.KG_Total_Quality+"' "+
                     // "    ,[Batida_Diaria] = '"+balanca.Batida_Diaria+"' "+
                      "    ,[Batida_Diaria_Quality] = '"+batida.Batida_Diaria_Quality+"' "+
                      " WHERE [E3TimeStamp] = '" + FormateDate(batida) + "' AND [Descricao] = '"+batida.Descricao+"'  ";

            using (SqlConnection connection =
                   new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = batidaUpdate;

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public IEnumerable<Batida> FindAll(Batida balanca)
        {
            throw new NotImplementedException();
        }


        public DataTable FindBy(Batida balanca, DateTime date)
        {
            string queryString =
                " SELECT [E3TimeStamp] ,[Descricao],[Batida], [KG_Total], [KG_Total_Atualizacao] " +
                " FROM [FabricaRacao].[fabrica].[Batidas]"+
                " WHERE convert(varchar, [E3TimeStamp], 103) = '"+date.ToShortDateString()+"' "+
                " AND descricao = '"+balanca.Descricao+"' ";

            using (SqlConnection connection =
                   new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;

                connection.Open();

                IList<Batida> batidas = new List<Batida>();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Batida batida = new Batida();
                        batida.Date = reader.GetDateTime(0);
                        batida.Milesegundos = reader.IsDBNull(0) ? "" : (
                        reader.GetDateTime(0).Millisecond.ToString().Length > 2 ? reader.GetDateTime(0).Millisecond.ToString() : "0" + reader.GetDateTime(0).Millisecond.ToString());
                        batida.Descricao = reader.IsDBNull(1) ? "" : reader.GetString(1);
                        batida.BatidaN = reader.IsDBNull(2)? 0 : reader.GetInt32(2);
                        batida.KG_Total = reader.IsDBNull(3) ? 0 : reader.GetDouble(3);
                        batida.KG_Total_Atualizado = reader.IsDBNull(4) ? 0 : reader.GetDouble(4);
                        batidas.Add(batida);
                    }

                    return RetornaBatidas(batidas);
                }
            }
        }

        public DataTable GetLastBeat(Batida balanca, DateTime date)
        {
            string queryString =
                " SELECT [E3TimeStamp] ,[Descricao],[Batida], " +
                " ( " +
                "     SELECT top 1 max([Batida_Diaria]) " +
                "    FROM [FabricaRacao].[fabrica].[Batidas] " +
                "     WHERE convert(varchar, [E3TimeStamp], 103) = '" + date.ToShortDateString() + "'  " +
                " ) AS [Batida_Diaria], " +
                " [KG_Total] " +
                " FROM [FabricaRacao].[fabrica].[Batidas] " +
                " WHERE convert(varchar, [E3TimeStamp], 103) = '" + date.ToShortDateString() + "' " +
                " AND descricao = '" + balanca.Descricao + "' " +
                " GROUP BY E3TimeStamp, Descricao, fabrica.Batidas.Batida,Batida_Diaria, [KG_Total]  " +
                " ORDER BY [E3TimeStamp] DESC ";


            using (SqlConnection connection =
                   new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;

                connection.Open();

                IList<Batida> batidas = new List<Batida>();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Batida batida = new Batida();
                        batida.Date = reader.GetDateTime(0);
                        batida.Milesegundos = reader.GetDateTime(0).Millisecond.ToString();
                        batida.Descricao = reader.IsDBNull(1) ? "" : reader.GetString(1);
                        batida.BatidaN = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);
                        batida.Batida_Diaria = reader.IsDBNull(3) ? 0 : reader.GetInt32(3);
                        batida.KG_Total = reader.IsDBNull(4) ? 0 : reader.GetDouble(4);
                        batidas.Add(batida);
                    }

                    return RetornaBatidasCadastro(batidas);
                }
            }
        }

        private DataColumn[] Colunas()
        {
            DataColumn[] columns = new DataColumn[5];

            DataColumn Date = new DataColumn();
            Date.ColumnName = "Data";
            columns[0] = Date;

            DataColumn Descricao = new DataColumn();
            Descricao.ColumnName = "Descricao";
            columns[1] = Descricao;

            DataColumn Batida = new DataColumn();
            Batida.ColumnName = "Batida";
            columns[2] = Batida;

            DataColumn KG_Total = new DataColumn();
            KG_Total.ColumnName = "KG_Total";
            columns[3] = KG_Total;

            DataColumn KG_Total_Atualizado = new DataColumn();
            KG_Total_Atualizado.ColumnName = "KG_Total_Atualizado";
            columns[4] = KG_Total_Atualizado;

            return columns;
        }


        private DataTable RetornaBatidas(IList<Batida> batidas)
        {

            DataSet dataSet = new DataSet();
            DataTable table = new DataTable();

            dataSet.Tables.Add(table);
            table.Columns.AddRange(Colunas());

            foreach (var bat in batidas)
            {
                DataRow row = table.NewRow();
                row["Data"] = bat.Date + "."+ bat.Milesegundos.ToString();
                row["Descricao"] = bat.Descricao;
                row["Batida"] = bat.BatidaN;
                row["KG_Total"] = bat.KG_Total;
                row["KG_Total_Atualizado"] = bat.KG_Total_Atualizado;
                table.Rows.Add(row);
            }

            return table;
        }

        private DataColumn[] ColunasCadastro()
        {
            DataColumn[] columns = new DataColumn[5];

            DataColumn Date = new DataColumn();
            Date.ColumnName = "Data";
            columns[0] = Date;

            DataColumn Descricao = new DataColumn();
            Descricao.ColumnName = "Descricao";
            columns[1] = Descricao;

            DataColumn Batida = new DataColumn();
            Batida.ColumnName = "Batida";
            columns[2] = Batida;

            DataColumn Batida_Diaria = new DataColumn();
            Batida_Diaria.ColumnName = "Bat.Diaria";
            columns[3] = Batida_Diaria;

            DataColumn Peso = new DataColumn();
            Peso.ColumnName = "Peso";
            columns[4] = Peso;

           

            return columns;
        }


        private DataTable RetornaBatidasCadastro(IList<Batida> batidas)
        {

            DataSet dataSet = new DataSet();
            DataTable table = new DataTable();

            dataSet.Tables.Add(table);
            table.Columns.AddRange(ColunasCadastro());

            foreach (var bat in batidas)
            {
                DataRow row = table.NewRow();
                row["Data"] = bat.Date + "." + bat.Milesegundos.ToString();
                row["Descricao"] = bat.Descricao;
                row["Batida"] = bat.BatidaN;
                row["Bat.Diaria"] = bat.Batida_Diaria;
                row["Peso"] = bat.KG_Total;
                table.Rows.Add(row);
            }

            return table;
        }

        private string FormateDate(Batida _batida)
        {
            //2010-11-20 13:38:43.140
            string ano = _batida.Date.Year.ToString();
            string mes = _batida.Date.Month > 9 ? _batida.Date.Month.ToString() : "0" + _batida.Date.Month.ToString();
            string dia = _batida.Date.Day > 9 ? _batida.Date.Day.ToString() : "0" + _batida.Date.Day.ToString();
            string hora = _batida.Date.Hour > 9 ? _batida.Date.Hour.ToString() : "0" + _batida.Date.Hour.ToString();
            string minutos = _batida.Date.Minute > 9 ? _batida.Date.Minute.ToString() : "0" + _batida.Date.Minute.ToString();
            string segundos = _batida.Date.Second > 9 ? _batida.Date.Second.ToString() : "0" + _batida.Date.Second.ToString();
            string millisecond = _batida.Date.Millisecond > 100 ? _batida.Date.Millisecond.ToString() : "00" + _batida.Date.Millisecond.ToString();



            return ano + "-" +
                mes + "-" +
                dia +
                " " +
                hora + ":" +
                minutos + ":" +
                segundos + ":" +
                millisecond;
        }
        
    }
}
